import pandas as pd
from util import mysql_utils
from util import dict_data_utils
from util import calculate
from concurrent.futures import ThreadPoolExecutor
import concurrent.futures
import time

pool = mysql_utils.get_mysql_pool()

big_batch_dict = dict_data_utils.get_big_batch_dict()
small_batch_dict = dict_data_utils.get_small_batch_dict()
nature_name_dict = dict_data_utils.get_nature_name_dict()
major_group_dict = dict_data_utils.get_major_group_dict()
type_dict = dict_data_utils.get_type_dict()


# 根据 大批次、 小批次、 院校代码 查询院校
def query_university(big_batch, small_batch, code):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT university_rank, type_name, province_code, city_code, province, city, nature_name, f211, f985, dual_class_name, dual_class_A, dual_class_B, dual_class_subject, is_dual_class, is_vocational_college, is_backbone_college FROM university_2_new WHERE big_batch={big_batch} AND small_batch={small_batch} AND code={code}"
            cursor.execute(sql_query)
            result = cursor.fetchone()
            # 将查询结果转换为字典
            if result is not None:
                result_dict = dict(
                    zip(['university_rank', 'type_name', 'province_code', 'city_code', 'province', 'city',
                         'nature_name', 'f211', 'f985',
                         'dual_class_name', 'dual_class_A', 'dual_class_B', 'dual_class_subject', 'is_dual_class',
                         'is_vocational_college', 'is_backbone_college'], result))
                return result_dict
            else:
                return None
    except Exception as e:
        print(f'查询院校基本信息异常：{e}')
        return None
    finally:
        conn.close()


def query_university_major_rank_mean_new(big_batch, small_batch, code, type, major_group, name):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT id FROM university_major_rank_mean_new WHERE big_batch={big_batch} AND small_batch={small_batch} AND code={code} AND type={type} AND major_group={major_group} AND name={name}"
            cursor.execute(sql_query)
            result = cursor.fetchone()
            return result
    except Exception as e:
        print(f'查询university_major_rank_mean_new信息异常：{e}')
        return None
    finally:
        conn.close()


def insert_university_major_rank_mean_new(insert_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                '''
                INSERT INTO university_major_rank_mean_new (
                    big_batch, small_batch, code, uname, type, major_group_code, major_group, major_code, name, 
                    old_name, plan_number, change_number, change_amplitude, change_grade, university_rank, 
                    mean_lowest_score, mean_lowest_rank, mean_mean_score, mean_mean_rank, assign_mean_rank, 
                    assign_lowest_rank, cut_remarks, type_name, nature_name, other_type, province_code, city_code, 
                    province, city, f985, f211, dual_class_A, dual_class_B, dual_class_subject, is_dual_class, 
                    is_vocational_college, is_backbone_college) 
                VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''', insert_values
            )
            conn.commit()
    except Exception as e:
        print(f'写入university_major_rank_mean_new异常：{e}')
        return None
    finally:
        conn.close()


def update_university_major_rank_mean_new(update_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                '''
                UPDATE university_major_rank_mean_new SET
                    big_batch=%s, small_batch=%s, code=%s, uname=%s, type=%s, major_group_code=%s, major_group=%s, 
                    major_code=%s, name=%s, old_name=%s, plan_number=%s, change_number=%s, change_amplitude=%s, 
                    change_grade=%s, university_rank=%s, mean_lowest_score=%s, mean_lowest_rank=%s, mean_mean_score=%s, 
                    mean_mean_rank=%s, assign_mean_rank=%s, assign_lowest_rank=%s, type_name=%s, nature_name=%s, 
                    other_type=%s, province_code=%s, city_code=%s, province=%s, city=%s, cut_remarks=%s WHERE id=%s
                ''', update_values)
            conn.commit()
    except Exception as e:
        print(f'更新major_rank_mean_new异常：{e}')
        return None
    finally:
        conn.close()


def query_university_major_rank_new(big_batch, small_batch, code, type, major_group, name, begin_year, end_year):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT year, lowest_score, lowest_rank, mean_score, mean_rank FROM university_major_rank_new WHERE big_batch={big_batch} AND small_batch={small_batch} AND code={code} AND type={type} AND major_group={major_group} AND name='{name}' AND year>={begin_year} AND year<={end_year} ORDER BY year DESC"
            # print(sql_query)
            cursor.execute(sql_query)
            result = cursor.fetchall()
            # 转换查询结果为字典列表
            result_dicts = [{'year': row[0], 'lowest_score': row[1], 'lowest_rank': row[2], 'mean_score': row[3],
                             'mean_rank': row[4]} for row in result]
            return result_dicts
    except Exception as e:
        print(f'查询university_major_rank_new异常：{e}')
        return None
    finally:
        conn.close()


def cal_change_grade(plan_number, change_number):
    if 1 <= plan_number <= 100:
        if change_number <= -51:
            return '缩减计划很多'
        elif -50 <= change_number <= -26:
            return '缩减计划较多'
        elif -25 <= change_number <= -1:
            return '缩减计划一般'
        elif 0 <= change_number < 1:
            return '与去年相同'
        elif 1 <= change_number <= 25:
            return '扩招计划一般'
        elif 26 <= change_number <= 50:
            return '扩招计划较多'
        elif 51 <= change_number:
            return '扩招计划很多'
        else:
            return ''
    elif 101 <= plan_number <= 300:
        if change_number <= -81:
            return '缩减计划很多'
        elif -80 <= change_number <= -41:
            return '缩减计划较多'
        elif -40 <= change_number <= -1:
            return '缩减计划一般'
        elif 0 <= change_number < 1:
            return '与去年相同'
        elif 1 <= change_number <= 40:
            return '扩招计划一般'
        elif 41 <= change_number <= 80:
            return '扩招计划较多'
        elif 81 <= change_number:
            return '扩招计划很多'
        else:
            return ''
    elif 301 <= plan_number:
        if change_number <= -261:
            return '缩减计划很多'
        elif -260 <= change_number <= -81:
            return '缩减计划较多'
        elif -80 <= change_number <= -1:
            return '缩减计划一般'
        elif 0 <= change_number < 1:
            return '与去年相同'
        elif 1 <= change_number <= 80:
            return '扩招计划一般'
        elif 81 <= change_number <= 260:
            return '扩招计划较多'
        elif 261 <= change_number:
            return '扩招计划很多'
        else:
            return ''
    else:
        return ''


def task(row):
    if row['批次'] in big_batch_dict.keys():
        big_batch = big_batch_dict[row['批次']]
    else:
        big_batch = 0

    if row['小批次'] in small_batch_dict.keys():
        small_batch = small_batch_dict[row['小批次']]
    else:
        small_batch = 0

    if row['院校代码'] and not pd.isna(row['院校代码']):
        code = row['院校代码']
    else:
        code = ""

    if row['院校名称'] and not pd.isna(row['院校名称']):
        uname = row['院校名称']
    else:
        uname = ""

    if row['首选科目'] in type_dict.keys():
        type = type_dict[row['首选科目']]
    else:
        type = 0

    if row['专业组编号'] and not pd.isna(row['专业组编号']):
        major_group_code = row['专业组编号']
    else:
        major_group_code = ""

    if row['专业组名称'] in major_group_dict.keys():
        major_group = major_group_dict[row['专业组名称']]
    else:
        major_group = 0

    if row['专业编号'] and not pd.isna(row['专业编号']):
        major_code = row['专业编号']
    else:
        major_code = ""

    if row['专业名称'] and not pd.isna(row['专业名称']):
        name = row['专业名称'].strip()
    else:
        name = ""

    if row['2023计划数'] and not pd.isna(row['2023计划数']) and (row['2023计划数'] != "0.0" or row['2023计划数'] != "0") and (
            row['2023计划数'] != 0.0 or row['2023计划数'] != 0):
        try:
            plan_number = int(row['2023计划数'])
        except Exception as e:
            print(f'2023计划数：{e}')
            plan_number = 0
    else:
        plan_number = 0

    if row['较去年增减数'] and not pd.isna(row['较去年增减数']):
        try:
            change_number = int(str(row['较去年增减数']).split(".", maxsplit=1)[0])
        except Exception as e:
            print(f'较去年增减数：{e}')
            change_number = 0
    else:
        change_number = 0

    if row['增减幅度'] and not pd.isna(row['增减幅度']):
        try:
            # change_amplitude = str(float(str(row['增减幅度'])) * 100) + "%"
            change_amplitude = f"{float(str(row['增减幅度'])) * 100:.2f}%"
        except Exception as e:
            print(f'增减幅度：{e}')
            change_amplitude = "0%"
    else:
        change_amplitude = "0%"

    change_grade = cal_change_grade(plan_number, change_number)

    if row['专业详情'] and not pd.isna(row['专业详情']):
        cut_remarks = row['专业详情'].strip()
    else:
        cut_remarks = ""

    # 近三年所有专业录取最低分、录取最低排名、录取平均分、录取平均排名
    begin_year = 2020
    end_year = 2022
    major_data_rank = query_university_major_rank_new(big_batch, small_batch, code, type, major_group, name, begin_year,
                                                      end_year)
    # print(major_data_rank)
    # 计算专业近三年录取平均分的平均值、近三年录取最低分平均值、近三年录取最低排名平均值、近三年录取平均排名平均值
    mean_lowest_score = calculate.mean_lowest_score(major_data_rank)
    mean_lowest_rank = calculate.mean_lowest_rank(major_data_rank)
    mean_mean_score = calculate.mean_mean_score(major_data_rank)
    mean_mean_rank = calculate.mean_mean_rank(major_data_rank)
    assign_mean_rank = calculate.assign_mean_rank(major_data_rank)
    assign_lowest_rank = calculate.assign_lowest_rank(major_data_rank, type)

    # 获取院校信息
    university_info = query_university(big_batch, small_batch, code)
    if university_info:
        university_rank = university_info['university_rank']
        type_name = university_info['type_name']
        province_code = university_info['province_code']
        city_code = university_info['city_code']
        province = university_info['province']
        city = university_info['city']
        nature_name = university_info['nature_name']
        f211 = university_info['f211']
        f985 = university_info['f985']
        dual_class_A = university_info['dual_class_A']
        dual_class_B = university_info['dual_class_B']
        dual_class_subject = university_info['dual_class_subject']
        is_dual_class = university_info['is_dual_class']
        is_vocational_college = university_info['is_vocational_college']
        is_backbone_college = university_info['is_backbone_college']
    else:
        university_rank = 999
        type_name = ''
        province_code = ''
        city_code = ''
        province = ''
        city = ''
        nature_name = 0
        f211 = 0
        f985 = 0
        dual_class_A = 0
        dual_class_B = 0
        dual_class_subject = 0
        is_dual_class = 0
        is_vocational_college = 0
        is_backbone_college = 0

    old_name = ''
    other_type = 0
    insert_values = (
        big_batch, small_batch, code, uname, type, major_group_code, major_group, major_code, name, old_name,
        plan_number, change_number, change_amplitude, change_grade, university_rank, mean_lowest_score,
        mean_lowest_rank, mean_mean_score, mean_mean_rank, assign_mean_rank, assign_lowest_rank, cut_remarks,
        type_name, nature_name, other_type, province_code, city_code, province, city, f985, f211, dual_class_A,
        dual_class_B, dual_class_subject, is_dual_class, is_vocational_college, is_backbone_college
    )
    print(insert_values)
    insert_university_major_rank_mean_new(insert_values)


if __name__ == "__main__":
    data = pd.read_excel("../static/excel_2024/交第2次更新-2023计划匹配指南-20240517.xlsx", header=0)
    start_time = int(time.time())
    print(f'开始：{start_time}')
    print(f'数据长度：{len(data)}')

    with ThreadPoolExecutor(max_workers=50) as executor:
        futures = [executor.submit(task, row) for index, row in data.iterrows()]
        # for future in futures:
        #     print(future)

    end_time = int(time.time())
    count_time = end_time - start_time
    print(f'结束：{end_time}')
    print(f'耗时：{count_time}')
